<?php
require_once '../includes/dbcon.php';

// Check if user is logged in (optional but recommended)
if (!is_logged_in()) {
    redirect('login.php');
}

// Initialize variables
$message = '';
$error = '';
$acyear = $klass = $subject = $term = '';
$preview_data = [];
$record_count = 0;
$show_preview = false;

// Fetch data for dropdowns
try {
    // Fetch academic years
    $stmt_years = $DBcon->query("SELECT DISTINCT academic_year FROM calender ORDER BY academic_year DESC");
    $years = $stmt_years->fetchAll(PDO::FETCH_ASSOC);
    
    // Fetch classes
    $stmt_classes = $DBcon->query("SELECT DISTINCT classid FROM class ORDER BY classid");
    $classes = $stmt_classes->fetchAll(PDO::FETCH_ASSOC);
    
    // Fetch subjects
    $stmt_subjects = $DBcon->query("SELECT subject_id, subjectname FROM subjectss ORDER BY subjectname");
    $subjects = $stmt_subjects->fetchAll(PDO::FETCH_ASSOC);
    
} catch (PDOException $e) {
    $error = "Error fetching dropdown data: " . $e->getMessage();
}

// Handle form submission
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    // Get and sanitize form data
    $acyear = sanitize_input($_POST['acyear'] ?? '');
    $klass = sanitize_input($_POST['klass'] ?? '');
    $subject = sanitize_input($_POST['subject'] ?? '');
    $term = sanitize_input($_POST['term'] ?? '');
    
    // Validate all fields are selected
    if (empty($acyear) || empty($klass) || empty($subject) || empty($term)) {
        $error = "All fields are required!";
    } else {
        // Check which button was clicked
        if (isset($_POST['preview'])) {
            // Preview button clicked - show records without deleting
            $show_preview = true;
            
            try {
                // Count records
                $count_stmt = $DBcon->prepare("
                    SELECT COUNT(*) as count 
                    FROM marks 
                    WHERE acyear = ? 
                    AND klass = ? 
                    AND subject = ? 
                    AND term = ?
                ");
                $count_stmt->execute([$acyear, $klass, $subject, $term]);
                $result = $count_stmt->fetch(PDO::FETCH_ASSOC);
                $record_count = $result['count'];
                
                if ($record_count > 0) {
                    // Fetch preview records
                    $preview_stmt = $DBcon->prepare("
                        SELECT sn, regno, test, exam, admin, created_at 
                        FROM marks 
                        WHERE acyear = ? 
                        AND klass = ? 
                        AND subject = ? 
                        AND term = ?
                        ORDER BY regno
                        LIMIT 50
                    ");
                    $preview_stmt->execute([$acyear, $klass, $subject, $term]);
                    $preview_data = $preview_stmt->fetchAll(PDO::FETCH_ASSOC);
                    
                    $message = "Found $record_count record(s) matching your criteria. Previewing first " . min(50, $record_count) . " records.";
                } else {
                    $error = "No records found matching the selected criteria.";
                    $show_preview = false;
                }
                
            } catch (PDOException $e) {
                $error = "Error fetching preview: " . $e->getMessage();
            }
            
        } elseif (isset($_POST['delete']) && isset($_POST['csrf_token'])) {
            // Delete button clicked - perform deletion
            // CSRF protection
            if (!validate_csrf_token($_POST['csrf_token'])) {
                $error = "Invalid CSRF token.";
            } else {
                try {
                    // Start transaction
                    $DBcon->beginTransaction();
                    
                    // First, count records that will be deleted
                    $count_stmt = $DBcon->prepare("
                        SELECT COUNT(*) as count 
                        FROM marks 
                        WHERE acyear = ? 
                        AND klass = ? 
                        AND subject = ? 
                        AND term = ?
                    ");
                    $count_stmt->execute([$acyear, $klass, $subject, $term]);
                    $result = $count_stmt->fetch(PDO::FETCH_ASSOC);
                    $record_count = $result['count'];
                    
                    if ($record_count > 0) {
                        // Delete records
                        $delete_stmt = $DBcon->prepare("
                            DELETE FROM marks 
                            WHERE acyear = ? 
                            AND klass = ? 
                            AND subject = ? 
                            AND term = ?
                        ");
                        $delete_stmt->execute([$acyear, $klass, $subject, $term]);
                        
                        $DBcon->commit();
                        
                        $message = "Successfully deleted $record_count record(s) for: 
                                   Academic Year: $acyear, 
                                   Class: $klass, 
                                   Subject: $subject, 
                                   Term: $term";
                        
                        // Clear form after successful deletion
                        $acyear = $klass = $subject = $term = '';
                        $preview_data = [];
                        $show_preview = false;
                    } else {
                        $error = "No records found matching the selected criteria.";
                    }
                    
                } catch (PDOException $e) {
                    $DBcon->rollBack();
                    $error = "Error deleting records: " . $e->getMessage();
                }
            }
        }
    }
}

// Generate CSRF token
$csrf_token = generate_csrf_token();
?>

<!DOCTYPE html>
<html lang="en">
<head>
  <!-- Required meta tags -->
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  <title>Delete Marks - School Admin</title>
  <!-- plugins:css -->
  <link rel="stylesheet" href="vendors/iconfonts/font-awesome/css/all.min.css">
  <link rel="stylesheet" href="vendors/css/vendor.bundle.base.css">
  <link rel="stylesheet" href="vendors/css/vendor.bundle.addons.css">
  <!-- endinject -->
  <!-- inject:css -->
  <link rel="stylesheet" href="css/style.css">
  <!-- endinject -->
  <style>
    .preview-table {
      max-height: 400px;
      overflow-y: auto;
    }
    .record-count-badge {
      font-size: 0.9rem;
      padding: 5px 10px;
    }
    .total-row {
      background-color: #f8f9fa;
      font-weight: bold;
    }
    .scroll-indicator {
      position: sticky;
      bottom: 0;
      background: rgba(0,0,0,0.7);
      color: white;
      padding: 5px;
      text-align: center;
      font-size: 0.8rem;
    }
  </style>
</head>
<body>
  <div class="container-scroller">
    <?php include("header.php"); ?>
    <div class="container-fluid page-body-wrapper">
      <?php include("sidebar.php"); ?>
      <div class="main-panel">
        <div class="content-wrapper">
          <div class="page-header">
            <h3 class="page-title">
              <i class="fas fa-trash-alt text-danger"></i>
              Delete Marks Records
            </h3>
          </div>
          
          <div class="row">
            <div class="col-md-12 grid-margin stretch-card">
              <div class="card">
                <div class="card-body">
                  <h4 class="card-title">Select Criteria for Deletion</h4>
                  <p class="card-description text-warning">
                    <i class="fas fa-exclamation-triangle"></i>
                    Warning: Deletion is permanent. Preview records before deleting.
                  </p>
                  
                  <!-- Display messages -->
                  <?php if (!empty($message)): ?>
                    <div class="alert alert-success alert-dismissible fade show" role="alert">
                      <?php echo $message; ?>
                      <button type="button" class="close" data-dismiss="alert" aria-label="Close">
                        <span aria-hidden="true">&times;</span>
                      </button>
                    </div>
                  <?php endif; ?>
                  
                  <?php if (!empty($error)): ?>
                    <div class="alert alert-danger alert-dismissible fade show" role="alert">
                      <?php echo $error; ?>
                      <button type="button" class="close" data-dismiss="alert" aria-label="Close">
                        <span aria-hidden="true">&times;</span>
                      </button>
                    </div>
                  <?php endif; ?>
                  
                  <form class="forms-sample" method="POST" action="">
                    <input type="hidden" name="csrf_token" value="<?php echo $csrf_token; ?>">
                    
                    <div class="row">
                      <div class="col-md-6">
                        <div class="form-group">
                          <label for="acyear">Academic Year *</label>
                          <select class="form-control" id="acyear" name="acyear" required>
                            <option value="">-- Select Academic Year --</option>
                            <?php foreach ($years as $year): ?>
                              <option value="<?php echo htmlspecialchars($year['academic_year']); ?>" 
                                <?php echo ($acyear == $year['academic_year']) ? 'selected' : ''; ?>>
                                <?php echo htmlspecialchars($year['academic_year']); ?>
                              </option>
                            <?php endforeach; ?>
                          </select>
                        </div>
                      </div>
                      
                      <div class="col-md-6">
                        <div class="form-group">
                          <label for="klass">Class *</label>
                          <select class="form-control" id="klass" name="klass" required>
                            <option value="">-- Select Class --</option>
                            <?php foreach ($classes as $class): ?>
                              <option value="<?php echo htmlspecialchars($class['classid']); ?>" 
                                <?php echo ($klass == $class['classid']) ? 'selected' : ''; ?>>
                                <?php echo htmlspecialchars($class['classid']); ?>
                              </option>
                            <?php endforeach; ?>
                          </select>
                        </div>
                      </div>
                    </div>
                    
                    <div class="row">
                      <div class="col-md-6">
                        <div class="form-group">
                          <label for="subject">Subject *</label>
                          <select class="form-control" id="subject" name="subject" required>
                            <option value="">-- Select Subject --</option>
                            <?php foreach ($subjects as $subject_item): ?>
                              <option value="<?php echo htmlspecialchars($subject_item['subjectname']); ?>" 
                                <?php echo ($subject == $subject_item['subjectname']) ? 'selected' : ''; ?>>
                                <?php echo htmlspecialchars($subject_item['subjectname']); ?>
                              </option>
                            <?php endforeach; ?>
                          </select>
                        </div>
                      </div>
                      
                      <div class="col-md-6">
                        <div class="form-group">
                          <label for="term">Term *</label>
                          <select class="form-control" id="term" name="term" required>
                            <option value="">-- Select Term --</option>
                            <option value="FIRST TERM" <?php echo ($term == 'FIRST TERM') ? 'selected' : ''; ?>>FIRST TERM</option>
                            <option value="SECOND TERM" <?php echo ($term == 'SECOND TERM') ? 'selected' : ''; ?>>SECOND TERM</option>
                            <option value="THIRD TERM" <?php echo ($term == 'THIRD TERM') ? 'selected' : ''; ?>>THIRD TERM</option>
                            <option value="MOCK" <?php echo ($term == 'MOCK') ? 'selected' : ''; ?>>MOCK</option>
                          </select>
                        </div>
                      </div>
                    </div>
                    
                    <div class="form-group mt-4">
                      <button type="submit" name="preview" class="btn btn-info mr-2">
                        <i class="fas fa-search"></i> Preview Records
                      </button>
                      
                      <?php if ($show_preview && $record_count > 0): ?>
                        <button type="submit" name="delete" class="btn btn-danger mr-2" 
                                onclick="return confirmDelete()">
                          <i class="fas fa-trash-alt"></i> Delete <?php echo $record_count; ?> Record(s)
                        </button>
                      <?php endif; ?>
                      
                      <button type="button" class="btn btn-light" onclick="clearForm()">
                        <i class="fas fa-eraser"></i> Clear Form
                      </button>
                    </div>
                  </form>
                </div>
              </div>
            </div>
          </div>
          
          <!-- Preview Section -->
          <?php if ($show_preview && !empty($preview_data)): ?>
            <div class="row">
              <div class="col-md-12 grid-margin stretch-card">
                <div class="card">
                  <div class="card-body">
                    <h4 class="card-title">
                      <i class="fas fa-eye text-info"></i>
                      Preview of Records to be Deleted
                      <span class="badge badge-danger record-count-badge float-right">
                        Total: <?php echo $record_count; ?> record(s)
                      </span>
                    </h4>
                    
                    <div class="alert alert-warning">
                      <i class="fas fa-info-circle"></i>
                      Showing first <?php echo count($preview_data); ?> of <?php echo $record_count; ?> records. 
                      <?php if ($record_count > 50): ?>
                        <strong>Only first 50 records shown. All <?php echo $record_count; ?> records will be deleted.</strong>
                      <?php endif; ?>
                    </div>
                    
                    <div class="preview-table">
                      <div class="table-responsive">
                        <table class="table table-hover table-striped">
                          <thead class="thead-dark">
                            <tr>
                              <th>#</th>
                              <th>Reg No</th>
                              <th>Test Score</th>
                              <th>Exam Score</th>
                              <th>Total</th>
                              <th>Admin</th>
                              <th>Created At</th>
                            </tr>
                          </thead>
                          <tbody>
                            <?php 
                            $total_test = 0;
                            $total_exam = 0;
                            $total_combined = 0;
                            $counter = 1;
                            
                            foreach ($preview_data as $record): 
                              $test_score = floatval($record['test']);
                              $exam_score = floatval($record['exam']);
                              $total_score = $test_score + $exam_score;
                              
                              $total_test += $test_score;
                              $total_exam += $exam_score;
                              $total_combined += $total_score;
                            ?>
                              <tr>
                                <td><?php echo $counter++; ?></td>
                                <td><?php echo htmlspecialchars($record['regno']); ?></td>
                                <td><?php echo number_format($test_score, 2); ?></td>
                                <td><?php echo number_format($exam_score, 2); ?></td>
                                <td><strong><?php echo number_format($total_score, 2); ?></strong></td>
                                <td><?php echo htmlspecialchars($record['admin']); ?></td>
                                <td><?php echo date('M d, Y', strtotime($record['created_at'])); ?></td>
                              </tr>
                            <?php endforeach; ?>
                            
                            <!-- Summary Row -->
                            <tr class="total-row">
                              <td colspan="2" class="text-right"><strong>Totals (Preview):</strong></td>
                              <td><strong><?php echo number_format($total_test, 2); ?></strong></td>
                              <td><strong><?php echo number_format($total_exam, 2); ?></strong></td>
                              <td><strong><?php echo number_format($total_combined, 2); ?></strong></td>
                              <td colspan="2">
                                <span class="badge badge-warning">
                                  Avg Total: <?php echo count($preview_data) > 0 ? number_format($total_combined / count($preview_data), 2) : '0.00'; ?>
                                </span>
                              </td>
                            </tr>
                            
                            <?php if ($record_count > count($preview_data)): ?>
                              <tr class="bg-light">
                                <td colspan="7" class="text-center text-muted">
                                  <i class="fas fa-ellipsis-h"></i>
                                  ... and <?php echo ($record_count - count($preview_data)); ?> more record(s) not shown ...
                                  <i class="fas fa-ellipsis-h"></i>
                                </td>
                              </tr>
                            <?php endif; ?>
                          </tbody>
                        </table>
                      </div>
                    </div>
                    
                    <div class="mt-3">
                      <div class="alert alert-danger">
                        <h5><i class="fas fa-exclamation-triangle"></i> Deletion Warning</h5>
                        <p>
                          You are about to delete <strong><?php echo $record_count; ?> record(s)</strong> permanently.<br>
                          This action cannot be undone. Please verify the criteria is correct:
                        </p>
                        <ul>
                          <li><strong>Academic Year:</strong> <?php echo htmlspecialchars($acyear); ?></li>
                          <li><strong>Class:</strong> <?php echo htmlspecialchars($klass); ?></li>
                          <li><strong>Subject:</strong> <?php echo htmlspecialchars($subject); ?></li>
                          <li><strong>Term:</strong> <?php echo htmlspecialchars($term); ?></li>
                        </ul>
                      </div>
                    </div>
                    
                    <div class="text-center mt-4">
                      <form method="POST" style="display: inline;">
                        <input type="hidden" name="csrf_token" value="<?php echo $csrf_token; ?>">
                        <input type="hidden" name="acyear" value="<?php echo htmlspecialchars($acyear); ?>">
                        <input type="hidden" name="klass" value="<?php echo htmlspecialchars($klass); ?>">
                        <input type="hidden" name="subject" value="<?php echo htmlspecialchars($subject); ?>">
                        <input type="hidden" name="term" value="<?php echo htmlspecialchars($term); ?>">
                        
                        <button type="submit" name="delete" class="btn btn-danger btn-lg mr-3" onclick="return confirmDelete()">
                          <i class="fas fa-trash-alt"></i> CONFIRM DELETE <?php echo $record_count; ?> RECORD(S)
                        </button>
                      </form>
                      
                      <button type="button" class="btn btn-secondary btn-lg" onclick="cancelDelete()">
                        <i class="fas fa-times"></i> Cancel
                      </button>
                    </div>
                  </div>
                </div>
              </div>
            </div>
          <?php endif; ?>
          
          <!-- Instructions Panel -->
          <div class="row">
            <div class="col-md-12 grid-margin stretch-card">
              <div class="card">
                <div class="card-body">
                  <h4 class="card-title">
                    <i class="fas fa-info-circle text-info"></i>
                    How to Use This Tool
                  </h4>
                  <div class="row">
                    <div class="col-md-6">
                      <h6>Step-by-Step Process:</h6>
                      <ol>
                        <li><strong>Select Criteria:</strong> Choose Academic Year, Class, Subject, and Term</li>
                        <li><strong>Preview:</strong> Click "Preview Records" to see what will be deleted</li>
                        <li><strong>Review:</strong> Carefully check the preview table</li>
                        <li><strong>Confirm:</strong> If correct, click "Confirm Delete"</li>
                        <li><strong>Cancel:</strong> If not correct, change criteria and preview again</li>
                      </ol>
                    </div>
                    <div class="col-md-6">
                      <h6>Safety Features:</h6>
                      <ul>
                        <li>Preview before deletion</li>
                        <li>Record count display</li>
                        <li>Confirmation dialogs</li>
                        <li>CSRF protection</li>
                        <li>Transaction rollback on error</li>
                      </ul>
                      <div class="alert alert-info">
                        <i class="fas fa-lightbulb"></i>
                        <strong>Tip:</strong> Always preview before deleting to avoid accidental data loss.
                      </div>
                    </div>
                  </div>
                </div>
              </div>
            </div>
          </div>
          
          <footer class="footer">
            <div class="d-sm-flex justify-content-center justify-content-sm-between">
              <span class="text-muted text-center text-sm-left d-block d-sm-inline-block">
                Delete Marks Module - Preview Enabled
              </span>
              <span class="float-none float-sm-right d-block mt-1 mt-sm-0 text-center">
                <i class="fas fa-shield-alt text-success"></i> Secure Preview System
              </span>
            </div>
          </footer>
        </div>
      </div>
    </div>
  </div>
  
  <!-- plugins:js -->
  <script src="vendors/js/vendor.bundle.base.js"></script>
  <script src="vendors/js/vendor.bundle.addons.js"></script>
  <!-- endinject -->
  <!-- inject:js -->
  <script src="js/off-canvas.js"></script>
  <script src="js/hoverable-collapse.js"></script>
  <script src="js/misc.js"></script>
  <!-- endinject -->
  
  <script>
    // Clear form function
    function clearForm() {
      document.getElementById('acyear').selectedIndex = 0;
      document.getElementById('klass').selectedIndex = 0;
      document.getElementById('subject').selectedIndex = 0;
      document.getElementById('term').selectedIndex = 0;
    }
    
    // Enhanced confirmation for delete
    function confirmDelete() {
      const acyear = document.getElementById('acyear')?.value || '<?php echo htmlspecialchars($acyear); ?>';
      const klass = document.getElementById('klass')?.value || '<?php echo htmlspecialchars($klass); ?>';
      const subject = document.getElementById('subject')?.value || '<?php echo htmlspecialchars($subject); ?>';
      const term = document.getElementById('term')?.value || '<?php echo htmlspecialchars($term); ?>';
      const count = <?php echo $record_count; ?>;
      
      return confirm(`🚨 FINAL CONFIRMATION 🚨\n\n` +
                    `You are about to PERMANENTLY DELETE ${count} record(s).\n\n` +
                    `Criteria:\n` +
                    `• Academic Year: ${acyear}\n` +
                    `• Class: ${klass}\n` +
                    `• Subject: ${subject}\n` +
                    `• Term: ${term}\n\n` +
                    `THIS ACTION CANNOT BE UNDONE!\n\n` +
                    `Click OK to proceed with deletion.\n` +
                    `Click Cancel to abort.`);
    }
    
    // Cancel and clear form
    function cancelDelete() {
      clearForm();
      window.scrollTo(0, 0);
    }
    
    // Auto-scroll to preview section when shown
    <?php if ($show_preview): ?>
      document.addEventListener('DOMContentLoaded', function() {
        const previewSection = document.querySelector('.preview-table');
        if (previewSection) {
          previewSection.scrollIntoView({ behavior: 'smooth' });
        }
      });
    <?php endif; ?>
  </script>
</body>
</html>